package com.kehutong.payfees.admin;

import java.io.File;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.temporal.TemporalAdjusters;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import com.kehutong.common.util.Token;
import com.kehutong.payfees.dao.ChargeBillDao;
import com.kehutong.payfees.util.BasicClient;
import com.kehutong.payfees.util.ExcelUtil;
import com.kehutong.payfees.util.JDBCUtil;
import org.coraframework.authz.HasPermission;
import org.coraframework.converter.util.StringUtil;
import org.coraframework.inject.Inject;
import org.coraframework.json.JSON;
import org.coraframework.json.JSONArray;
import org.coraframework.json.JSONObject;
import org.coraframework.mvc.MvcAction;
import org.coraframework.mvc.http.ReqMapping;
import org.coraframework.mvc.http.result.Result;
import org.coraframework.orm.Pool;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.Page;

import com.kehutong.common.ExcelHeaders;
import com.kehutong.common.Java8TimeUtils;
import com.kehutong.common.PageUtils;
import com.kehutong.common.math.CalculatorUtils;
import com.kehutong.payfees.entity.ChargeBill;
import com.kehutong.payfees.entity.ChargeBillAnnex;
import com.kehutong.payfees.entity.ChargeBillItem;
import com.kehutong.payfees.enums.BillEnableState;
import com.kehutong.payfees.enums.BillPaidState;
import com.kehutong.payfees.enums.BillPayMethod;
import com.kehutong.payfees.service.ChargeBillServiceImpl;
import org.coraframework.orm.jdbc.execute.Condition;
import org.coraframework.orm.jdbc.execute.QueryCondition;
import org.coraframework.orm.jdbc.execute.entity.FindArray;
import org.coraframework.orm.jdbc.execute.entity.FindPage;
import org.coraframework.util.Objects;

/**
 * Author: peter Date: 2020/7/17 Function:缴费报表模块
 */
@MvcAction
@ReqMapping("/admin/charge/statistics")
public class ChargeStatisticsAction {

	@Inject
	private ChargeBillServiceImpl chargeBillService;
	@Inject
	private JdbcSession jdbcSession;
	@Inject
	private ChargeBillDao chargeBillDao;
	@Inject
	BasicClient basicClient;

	/**
	 * 数据看板-缴费账单统计
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/kanban/bill_data")
	public Object billData(Token token, JSONObject req) throws Exception {

		List<ChargeBill> bills = getBillsForKanban(token, req);

		JSONObject r = new JSONObject();

		// 应收金额
		BigDecimal shouldReceiveAmt = chargeBillService.sumTotal(bills);
		r.put("shouldReceiveAmt", shouldReceiveAmt);
		r.put("shouldReceiveCount", bills.size());

		// 待收金额
		BigDecimal unReceiveAmt = chargeBillService.sumUnPaidTotal(bills);
		r.put("unReceiveAmt", unReceiveAmt);
		r.put("unReceiveCount", bills.stream().filter(bill -> bill.getPaidState() == BillPaidState.UNPAID).count());

		// 已收金额
		BigDecimal receivedAmt = chargeBillService.sumPaidTotal(bills);
		r.put("receivedAmt", receivedAmt);
		r.put("receivedCount", bills.stream().filter(bill -> bill.getPaidState() == BillPaidState.PAID).count());

		// 收缴率 = 已收金额 / 应收金额 * 100%
		r.put("ratio", CalculatorUtils.percentage(receivedAmt, shouldReceiveAmt));

		return Result.success(r);
	}

	private List<ChargeBill> getBillsForKanban(Token token, JSONObject req) throws Exception {
		FindArray<ChargeBill> findBill = jdbcSession.findArray(ChargeBill.class);

		JDBCUtil.between(req, findBill,"shouldPayDate");

		String departmentId = req.getString("departmentId");
		if (StringUtil.isNotBlank(departmentId)) {
			List<String> departmentIds = basicClient.getDepartmentIdsFilterList(token, departmentId);
			findBill.in("communityNo", departmentIds);
		}

		return findBill.eq("enableState", BillEnableState.ENABLE).eq("deleted", false).exe();
	}

	/**
	 * 数据看板-缴费方式统计
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/kanban/pay_method_data")
	public Object payMethodData(Token token, JSONObject req) throws Exception {
		FindArray<ChargeBill> findBill = jdbcSession.findArray(ChargeBill.class);
		JDBCUtil.between(req, findBill, "shouldPayDate");

		String departmentId = req.getString("departmentId");
		if (StringUtil.isNotBlank(departmentId)) {
			List<String> departmentIds = basicClient.getDepartmentIdsFilterList(token, departmentId);
			findBill.in("communityNo", departmentIds);
		}

		List<ChargeBill> bills = findBill.eq("paidState", BillPaidState.PAID).eq("enableState", BillEnableState.ENABLE)
				.eq("deleted", false).exe();

		Map<BillPayMethod, List<ChargeBill>> groupedMap = bills.stream()
				.collect(Collectors.groupingBy(ChargeBill::getPayMethod));

		List<JSONObject> l = groupedMap.entrySet().stream().map(en -> {
			JSONObject r = new JSONObject();
			r.put("name", en.getKey().getDesc());
			r.put("value", en.getValue().size());
			return r;
		}).collect(Collectors.toList());

		return Result.success(l);
	}

	/**
	 * 数据看板-收缴率趋势图
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/kanban/year_chart")
	public Object getYearChart(Token token, JSONObject req) throws Exception {
		Integer year = req.getInteger("year");
		if (year == null) {
			return Result.error(0, "请选择年份");
		}

		LocalDate beginDate = LocalDate.now().withYear(year).with(TemporalAdjusters.firstDayOfYear());
		LocalDate endDate = beginDate.with(TemporalAdjusters.lastDayOfYear());

		FindArray<ChargeBill> findBill = jdbcSession.findArray(ChargeBill.class)
				.between("shouldPayDate", beginDate, endDate)
				.eq("enableState", BillEnableState.ENABLE).eq("deleted", false);

		String departmentId = req.getString("departmentId");
		if (StringUtil.isNotBlank(departmentId)) {
			List<String> departmentIds = basicClient.getDepartmentIdsFilterList(token, departmentId);
			if(Objects.nonNull(departmentIds)){findBill.in("communityNo", departmentIds);}
		}

		List<ChargeBill> allBills = findBill.exe();

		Map<Integer, List<ChargeBill>> monthGroupedBills = allBills.stream()
				.peek(bill -> bill.setBillMonth(bill.getShouldPayDate().getMonthValue()))
				.collect(Collectors.groupingBy(ChargeBill::getBillMonth));

		// 1-12月
		List<JSONObject> resultList = Stream.iterate(1, x -> ++x).limit(12).map(month -> {
			List<ChargeBill> bills = monthGroupedBills.get(month);
			if (bills == null) {
				bills = new ArrayList<>();
			}
			// 应收
			BigDecimal shouldReceiveAmt = chargeBillService.sumTotal(bills);
			// 已收
			BigDecimal receivedAmt = chargeBillService.sumPaidTotal(bills);
			// 收缴率
			BigDecimal ratio = CalculatorUtils.percentage(receivedAmt, shouldReceiveAmt);

			JSONObject v = new JSONObject();
			v.put("shouldReceiveAmt", shouldReceiveAmt);
			v.put("receivedAmt", receivedAmt);
			v.put("ratio", ratio);
			v.put("month", month);
			return v;
		}).sorted(Comparator.comparingInt(o -> o.getIntValue("month"))).collect(Collectors.toList());

		// 构造前端需要的三个数组
		JSONObject r = new JSONObject();
		r.put("shouldReceiveAmt",
				resultList.stream().map(j -> j.getBigDecimal("shouldReceiveAmt")).collect(Collectors.toList()));
		r.put("receivedAmt", resultList.stream().map(j -> j.getBigDecimal("receivedAmt")).collect(Collectors.toList()));
		r.put("ratio", resultList.stream().map(j -> j.getBigDecimal("ratio")).collect(Collectors.toList()));

		return Result.success(r);
	}

	/**
	 * 收缴率列表
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/pay_ratio/list")
	public Object listPayRatio(Token token, JSONObject req) throws Exception {
		List<JSONObject> resultList = chargeBillService.listPayRatioDescData(token, req);

		return Result.success(resultList);
	}

	/**
	 * 收缴率分页列表
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/pay_ratio/page")
	public Object pagePayRatio(Token token, JSONObject req) throws Exception {
		QueryCondition query = Condition.createQuery(false);

		String name = req.getString("name");
		if(Objects.nonEmpty(name)){
			query.like("communityName", name);
		}

		JDBCUtil.between(req, query, "shouldPayDate");
		query.eq("enableState", BillEnableState.ENABLE);
		query.eq("deleted", false);

		query.authorize("departmentNo","departmentNos");

		List<Map<String, Object>> list = chargeBillDao.getBillRatio(query);

		List<JSONObject> result = list.stream().map(map -> {
			return JSON.parseObject(JSON.toJSONString(map));
		}).collect(Collectors.toList());

		Page<JSONObject> page = PageUtils.buildPage(req, result);

		return Result.success(page);
	}

	/**
	 * 收缴率列表导出
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/pay_ratio/export")
	public Object exportPayRatio(Token token, JSONObject req) throws Exception {
        QueryCondition query = Condition.createQuery(false);

        String name = req.getString("name");
        if(Objects.nonEmpty(name)){
            query.like("communityName", name);
        }

        JDBCUtil.between(req, query, "shouldPayDate");
		query.eq("enableState", BillEnableState.ENABLE);
        query.eq("deleted", false);

        query.authorize("departmentNo","departmentNos");

        List<Map<String, Object>> list = chargeBillDao.getBillRatio(query);

        List<JSONObject> result = list.stream().map(map -> {
            return JSON.parseObject(JSON.toJSONString(map));
        }).collect(Collectors.toList());

		List<String[]> objList = new ArrayList<>(result.size());
		for (int i = 0; i < result.size(); i++) {
			JSONObject json = result.get(i);
			String[] l = new String[6];

			l[0] = String.valueOf(i + 1);
			l[1] = json.getString("name");
			l[2] = String.valueOf(json.getBigDecimal("shouldReceive"));
			l[3] = String.valueOf(json.getBigDecimal("received"));
			l[4] = String.valueOf(json.getBigDecimal("toReceive"));

			String ratioStr = "0.00%";
			BigDecimal ratio = json.getBigDecimal("ratio");
			if (ratio != null) {
				ratioStr = ratio + "%";
			}
			l[5] = ratioStr;
			objList.add(l);
		}

		File tmpFile = ExcelUtil.writeXlsFile("收缴率列表导出.xls",
				ExcelHeaders.CHARGE_STATISTICS_PAY_RATIO_EXPORT_EXCEL_HEADER, objList);
		return Result.download(tmpFile);
	}

	/**
	 * 收缴率 - 详情分页列表
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/pay_ratio/detail/page")
	public Object pagePayRatioDetail(Token token, JSONObject req) throws Exception {

		Map<String,JSONObject> communityMap = basicClient.getCommunityMap(token);

		JSONArray houses = basicClient.getHouses(token, req);
		List<JSONObject> resultList = houses.stream().map(item -> {
			JSONObject house = (JSONObject) item;
			 FindArray find = jdbcSession.findArray(ChargeBill.class)
                     .in(req, "communityNo")
					.in("houseNo", house.getString("id"))
					.eq("enableState", BillEnableState.ENABLE)
					.eq("deleted", false);

			JDBCUtil.between(req, find ,"shouldPayDate");
			List<ChargeBill> bills = find.exe();

			JSONObject v = new JSONObject();
			v.put("id", house.getString("id"));
			// 小区
			JSONObject community = communityMap.get(house.getString("communityNo"));
			if(Objects.nonEmpty(community)){
				v.put("communityName", community.getString("name"));
			}
			// 房屋信息
			v.put("buildingName", house.getString("buildName"));
			String unitName = house.getString("unitName");
			if(Objects.nonEmpty(unitName)){
				v.put("unitName", unitName);
			}
			v.put("houseName", house.getString("floorName") + house.getString("name"));
			// 业主信息
			v.put("ownerName", house.getString("ownerName"));
			v.put("ownerMobile", house.getString("ownerMobile"));

			// 应收
			BigDecimal shouldReceive = chargeBillService.sumTotal(bills);
			v.put("shouldReceive", shouldReceive);
			// 已收
			BigDecimal received = chargeBillService.sumPaidTotal(bills);
			v.put("received", received);
			// 待收
			BigDecimal toReceive = chargeBillService.sumUnPaidTotal(bills);
			v.put("toReceive", toReceive);

			return v;
		}).collect(Collectors.toList());

		Page<JSONObject> r = PageUtils.buildPage(req, resultList);

		return Result.success(r);
	}

	/**
	 * 收缴率 - 详情列表导出
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/pay_ratio/detail/export")
	public Object exportPayRatioDetail(Token token, JSONObject req) throws Exception {

		Map<String,JSONObject> communityMap = basicClient.getCommunityMap(token);

		JSONArray houses = basicClient.getHouses(token, req);
		List<String[]> objList = new ArrayList<>(houses.size());
		for (int i = 0; i < houses.size(); i++) {
			JSONObject h =(JSONObject) houses.get(i);
			FindArray<ChargeBill> find = jdbcSession.findArray(ChargeBill.class).in(req, "communityNo")
					.in("houseNo", h.getString("id"))
					.eq("enableState", BillEnableState.ENABLE)
					.eq("deleted", false);

			JDBCUtil.between(req, find, "shouldPayDate");

			List<ChargeBill> bills = find.exe();

			String[] l = new String[11];
			l[0] = String.valueOf(i + 1);
			l[1] = communityMap.get(h.getString("communityNo")).getString("name");
			l[2] = h.getString("buildName");
			l[3] = h.getString("unitName");
			l[4] = h.getString("floorName");
			l[5] = h.getString("name");

			l[6] = h.getString("ownerName");
			l[7] = h.getString("ownerMobile");

			// 应收
			BigDecimal shouldReceive = chargeBillService.sumTotal(bills);

			l[8] = String.valueOf(shouldReceive);
			// 已收
			BigDecimal received = chargeBillService.sumPaidTotal(bills);
			l[9] = String.valueOf(received);
			BigDecimal toReceive = chargeBillService.sumUnPaidTotal(bills);
			l[10] = String.valueOf(toReceive);

			objList.add(l);

		}

		File tmpFile = ExcelUtil.writeXlsFile("缴费账单列表导出.xls",
				ExcelHeaders.CHARGE_STATISTICS_PAY_RATIO_DETAIL_EXPORT_EXCEL_HEADER, objList);
		return Result.download(tmpFile);
	}

	/**
	 * 缴费账单列表 待收账单、已收账单
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/bill/page")
	public Object pageBill(Token token, JSONObject req) throws Exception {

		FindPage<ChargeBill> find = jdbcSession.findPage(ChargeBill.class)
				// 搜索条件 城市、架构、支付状态、支付方式
				.eq("deleted", false).eq("enableState", BillEnableState.ENABLE)
				.eq(req, "paidState", "payMethod").order("communityName", "ASC")
				.order("buildingName", "ASC").order("unitName", "ASC").order("floorName", "ASC")
				.order("houseName", "ASC").order("billDateFrom", "ASC");

		List<String> houseNos = chargeBillService.getHouseNosFilterList(token, req);
		if (houseNos != null) {
			if (houseNos.isEmpty()) {
				// 无筛选结果
				return Result.success(new Page<>());
			}
			find.in("houseNo", houseNos);
		}
		// 账单周期
		if (StringUtil.isNotBlank(req.getString("billDateFrom"))) {
			find.maxEq("billDateFrom", req.get("billDateFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("billDateTo"))) {
			find.minEq("billDateTo", req.get("billDateTo"));
		}
		// 应付款日
		if (StringUtil.isNotBlank(req.getString("shouldPayDateFrom"))) {
			find.maxEq("shouldPayDate", req.get("shouldPayDateFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("shouldPayDateTo"))) {
			find.minEq("shouldPayDate", req.get("shouldPayDateTo"));
		}
		// 付款时间
		if (StringUtil.isNotBlank(req.getString("paidAtFrom"))) {
			find.maxEq("paidAt", req.get("paidAtFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("paidAtTo"))) {
			find.minEq("paidAt", req.get("paidAtTo"));
		}

		Page<ChargeBill> page = find.page(req).exe();

		Map<String,JSONObject> communityMap = basicClient.getCommunityMap(token);

		List<JSONObject> list = page.getList().stream().map(bill -> {

			JSONObject v = new JSONObject();
			v.put("id", bill.getId());
			v.put("shouldPayDate", bill.getShouldPayDate());
			v.put("billDateFrom", bill.getBillDateFrom());
			v.put("billDateTo", bill.getBillDateTo());
			v.put("total", bill.getTotal());
			v.put("payMethod", bill.getPayMethod());
			v.put("paidAt", bill.getPaidAt());

			JSONObject result = null;
			try {
				result = basicClient.getHouseById(token, bill.getHouseNo());
			} catch (Exception e) {
			}

			JSONObject data = result;
			if (data != null) {
				v.put("communityName", communityMap.get(data.getString("communityNo")).getString("name"));
				v.put("buildingName", data.getString("buildName"));
				v.put("unitName", data.getString("unitName"));
				v.put("floorName", data.getString("floorName"));
				v.put("houseName", data.getString("name"));

				v.put("ownerName", data.getString("ownerName"));
				v.put("ownerMobile", data.getString("ownerMobile"));
			}
			return v;
		}).collect(Collectors.toList());

		Page<JSONObject> r = PageUtils.parse(page, list);

		return Result.success(r);
	}

	/**
	 * 缴费账单列表 待收账单导出
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/bill/unpaid/export")
	public Object exportUnPaidBill(Token token, JSONObject req) throws Exception {

		FindArray<ChargeBill> find = jdbcSession.findArray(ChargeBill.class)
				// 搜索条件 城市、架构、支付状态、支付方式
				.eq(req, "payMethod").eq("paidState", BillPaidState.UNPAID)
				.eq("enableState", BillEnableState.ENABLE).eq("deleted", false).order("communityName", "ASC")
				.order("buildingName", "ASC").order("unitName", "ASC").order("floorName", "ASC")
				.order("houseName", "ASC").order("billDateFrom", "ASC");

		List<String> houseNos = chargeBillService.getHouseNosFilterList(token, req);
		if (houseNos != null) {
			if (houseNos.isEmpty()) {
				// 无筛选结果
				return Result.success(new Page<>());
			}
			find.in("houseNo", houseNos);
		}
		// 账单周期
		if (StringUtil.isNotBlank(req.getString("billDateFrom"))) {
			find.maxEq("billDateFrom", req.get("billDateFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("billDateTo"))) {
			find.minEq("billDateTo", req.get("billDateTo"));
		}
		// 应付款日
		if (StringUtil.isNotBlank(req.getString("shouldPayDateFrom"))) {
			find.maxEq("shouldPayDate", req.get("shouldPayDateFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("shouldPayDateTo"))) {
			find.minEq("shouldPayDate", req.get("shouldPayDateTo"));
		}
		// 付款时间
		if (StringUtil.isNotBlank(req.getString("paidAtFrom"))) {
			find.maxEq("paidAt", req.get("paidAtFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("paidAtTo"))) {
			find.minEq("paidAt", req.get("paidAtTo"));
		}

		List<ChargeBill> bills = find.exe();

		Map<String,JSONObject> communityMap = basicClient.getCommunityMap(token);

		List<String[]> objList = new ArrayList<>(bills.size());
		for (int i = 0; i < bills.size(); i++) {
			ChargeBill b = bills.get(i);

			JSONObject house = basicClient.getHouseById(token,  b.getHouseNo());
			if (Objects.isNull(house)) {
				continue;
			}
			String[] l = new String[12];
			l[0] = String.valueOf(i + 1);
			l[1] = String.valueOf(b.getShouldPayDate());
			l[2] = communityMap.get(house.getString("communityNo")).getString("name");
			l[3] = house.getString("buildName");
			l[4] = house.getString("unitName");
			l[5] = house.getString("floorName");
			l[6] = house.getString("name");

			l[7] = house.getString("ownerName");
			l[8] = house.getString("ownerMobile");

			l[9] = String.valueOf(b.getBillDateFrom());
			l[10] = String.valueOf(b.getBillDateTo());
			l[11] = CalculatorUtils.formatMoneyString(b.getTotal());

			objList.add(l);
		}

		File tmpFile = ExcelUtil.writeXlsFile("待收账单列表导出.xls",
				ExcelHeaders.CHARGE_STATISTICS_UNPAID_BILL_EXPORT_EXCEL_HEADER, objList);

		return Result.download(tmpFile);
	}

	/**
	 * 缴费账单列表 已收账单导出
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/bill/paid/export")
	public Object exportPaidBill(Token token, JSONObject req) throws Exception {

		FindArray<ChargeBill> find = jdbcSession.findArray(ChargeBill.class)
				// 搜索条件 城市、架构、支付状态、支付方式
				.eq(req, "payMethod").eq("paidState", BillPaidState.PAID)

				.order("communityName", "ASC").order("buildingName", "ASC").order("unitName", "ASC")
				.order("floorName", "ASC").order("houseName", "ASC").order("billDateFrom", "ASC");

		List<String> houseNos = chargeBillService.getHouseNosFilterList(token, req);
		if (houseNos != null) {
			if (houseNos.isEmpty()) {
				// 无筛选结果
				return Result.success(new Page<>());
			}
			find.in("houseNo", houseNos);
		}
		// 账单周期
		if (StringUtil.isNotBlank(req.getString("billDateFrom"))) {
			find.maxEq("billDateFrom", req.get("billDateFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("billDateTo"))) {
			find.minEq("billDateTo", req.get("billDateTo"));
		}
		// 应付款日
		if (StringUtil.isNotBlank(req.getString("shouldPayDateFrom"))) {
			find.maxEq("shouldPayDate", req.get("shouldPayDateFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("shouldPayDateTo"))) {
			find.minEq("shouldPayDate", req.get("shouldPayDateTo"));
		}
		// 付款时间
		if (StringUtil.isNotBlank(req.getString("paidAtFrom"))) {
			find.maxEq("paidAt", req.get("paidAtFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("paidAtTo"))) {
			find.minEq("paidAt", req.get("paidAtTo"));
		}

		List<ChargeBill> bills = find.exe();

		Map<String,JSONObject> communityMap = basicClient.getCommunityMap(token);

		List<String[]> objList = new ArrayList<>(bills.size());
		for (int i = 0; i < bills.size(); i++) {
			ChargeBill b = bills.get(i);

			JSONObject house = basicClient.getHouseById(token,  b.getHouseNo());
			if (Objects.isNull(house)) {
				continue;
			}
			String[] l = new String[12];
			l[0] = String.valueOf(i + 1);
			l[1] = String.valueOf(b.getShouldPayDate());
			l[2] = communityMap.get(house.getString("communityNo")).getString("name");
			l[3] = house.getString("buildName");
			l[4] = house.getString("unitName");
			l[5] = house.getString("floorName");
			l[6] = house.getString("name");

			l[7] = String.valueOf(b.getBillDateFrom());
			l[8] = String.valueOf(b.getBillDateTo());
			l[9] = CalculatorUtils.formatMoneyString(b.getTotal());

			l[10] = Java8TimeUtils.parseString(b.getPaidAt());
			l[11] = b.getPayMethod().getDesc();

			objList.add(l);
		}

		File tmpFile = ExcelUtil.writeXlsFile("已收账单列表导出.xls",
				ExcelHeaders.CHARGE_STATISTICS_PAID_BILL_EXPORT_EXCEL_HEADER, objList);
		return Result.download(tmpFile);
	}

	/**
	 * 缴费账单详情
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/bill/get")
	public Object getBill(String id) {
		ChargeBill bill = Pool.get(ChargeBill.class, id);
		if (bill == null) {
			return Result.error(0, "对象不存在");
		}

		// 缴费科目列表
		List<ChargeBillItem> items = jdbcSession.findArray(ChargeBillItem.class).eq("billNo", bill.getId()).exe();
		bill.setBillItems(items);

		// 附件列表
		List<ChargeBillAnnex> annexes = jdbcSession.findArray(ChargeBillAnnex.class).eq("billNo", bill.getId()).exe();
		bill.setBillAnnexes(annexes);

		return Result.success(bill);
	}

	/**
	 * 缴费账单总金额
	 */
	@HasPermission("charge:statistics:view")
	@ReqMapping("/bill/total/get")
	public Object getBillsTotal(Token token, JSONObject req) throws Exception {

		FindArray<ChargeBill> find = jdbcSession.findArray(ChargeBill.class)
				// 搜索条件 城市、架构、支付状态、支付方式
				.eq(req,"paidState", "payMethod").eq("enableState", BillEnableState.ENABLE)
				.eq("deleted", false);

		List<String> houseNos = chargeBillService.getHouseNosFilterList(token, req);
		if (houseNos != null) {
			if (houseNos.isEmpty()) {
				// 无筛选结果
				return Result.success(0);
			}
			find.in("houseNo", houseNos);
		}
		// 账单周期
		if (StringUtil.isNotBlank(req.getString("billDateFrom"))) {
			find.maxEq("billDateFrom", req.get("billDateFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("billDateTo"))) {
			find.minEq("billDateTo", req.get("billDateTo"));
		}
		// 应付款日
		if (StringUtil.isNotBlank(req.getString("shouldPayDateFrom"))) {
			find.maxEq("shouldPayDate", req.get("shouldPayDateFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("shouldPayDateTo"))) {
			find.minEq("shouldPayDate", req.get("shouldPayDateTo"));
		}
		// 付款时间
		if (StringUtil.isNotBlank(req.getString("paidAtFrom"))) {
			find.maxEq("paidAt", req.get("paidAtFrom"));
		}
		if (StringUtil.isNotBlank(req.getString("paidAtTo"))) {
			find.minEq("paidAt", req.get("paidAtTo"));
		}

		List<ChargeBill> bills = find.exe();

		BigDecimal billsTotal = chargeBillService.sumTotal(bills);

		return Result.success(billsTotal);
	}
}
